﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using trangbaocao.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
using System.Data;
namespace trangbaocao.Models
{
    public class StationsModel
    {
        public int id { get; set; }
        [Required(ErrorMessage = "Tên trạm không được để trống!")]
        public string name { get; set; }
        public string dia_chi { get; set; }

        public int main_stations_id { get; set; }
        public SelectList trung_tam_id { get; set; }
        public string nguoi_quan_ly { get; set; }
        public string so_dien_thoai { get; set; }
        public string ngay_xay_dung { get; set; }
        public int tram_node_b { get; set; }
        public string ngay_su_dung { get; set; }
        public string ghi_chu { get; set; }

        [Required(ErrorMessage = "Mã trạm không được để trống!")]
        public string ma_tram { get; set; }
        public string toa_do { get; set; }
        public string cot { get; set; }
        public string cap_phep { get; set; }
        public string csht { get; set; }
        public string loai_csht { get; set; }
        public string kieu_csht { get; set; }

        [Required(ErrorMessage = "Không được để trống!")]
        public string village_id { get; set; }
        public string ngay_cap_phep { get; set; }
        public SelectList khuvucs_id { get; set; }

        public List<StationsModel> getStations()
        {
            List<StationsModel> Stations = new List<StationsModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM Stations order by name");
            while (odr.Read())
            {
                Stations.Add(new StationsModel 
                { 
                    id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), dia_chi = odr[2].ToString(), main_stations_id = Convert.ToInt32(odr[3].ToString()), nguoi_quan_ly = odr[4].ToString(), so_dien_thoai = odr[5].ToString(), ngay_xay_dung = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"), tram_node_b = Convert.ToInt16(odr[7].ToString()), ngay_su_dung = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"), ghi_chu = odr[9].ToString(),
                    ma_tram = odr["ma_tram"].ToString(),
                    toa_do = odr["toa_do"].ToString(),
                    cot = odr["cot"].ToString(),
                    cap_phep = odr["cap_phep"].ToString(),
                    csht = odr["csht"].ToString(),
                    loai_csht = odr["loai_csht"].ToString(),
                    kieu_csht = odr["kieu_csht"].ToString(),
                    village_id = odr["village_id"].ToString(),
                    ngay_cap_phep = odr.IsDBNull(18) ? null : odr.GetDateTime(18).ToString("dd/MM/yyyy")
                });
            }
            return Stations;
        }
        public List<StationsModel> getStationsByMain_Stations(int id)
        {
            List<StationsModel> Stations = new List<StationsModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM Stations where main_stations_id="+id+ " order by name");
            string sql = "SELECT * FROM Stations where main_stations_id=" + id + " order by id";
            while (odr.Read())
            {
                Stations.Add(new StationsModel 
                { 
                    id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), dia_chi = odr[2].ToString(), main_stations_id = Convert.ToInt32(odr[3].ToString()), nguoi_quan_ly = odr[4].ToString(), so_dien_thoai = odr[5].ToString(), ngay_xay_dung = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"), tram_node_b = Convert.ToInt16(odr[7].ToString()), ngay_su_dung = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"), ghi_chu = odr[9].ToString(),
                    ma_tram = odr["ma_tram"].ToString(),
                    toa_do = odr["toa_do"].ToString(),
                    cot = odr["cot"].ToString(),
                    cap_phep = odr["cap_phep"].ToString(),
                    csht = odr["csht"].ToString(),
                    loai_csht = odr["loai_csht"].ToString(),
                    kieu_csht = odr["kieu_csht"].ToString(),
                    village_id = odr["village_id"].ToString(),
                    ngay_cap_phep = odr.IsDBNull(18) ? null : odr.GetDateTime(18).ToString("dd/MM/yyyy")
                });
            }
            return Stations;
        }

        public void addStations(StationsModel Stations)
        {
            string sql = "INSERT INTO Stations";
            //sql += " select nvl((select max(id)+1 from Stations),1),'" + Stations.name + "','" + Stations.dia_chi + "'," + Stations.main_stations_id + ",'" + Stations.nguoi_quan_ly + "','" + Stations.so_dien_thoai + "',to_date('" + Stations.ngay_xay_dung + "','dd/mm/yyyy')," + Stations.tram_node_b + ",to_date('" + Stations.ngay_su_dung + "','dd/mm/yyyy'),'" + Stations.ghi_chu + "' from dual";
            sql += " select nvl((select max(id)+1 from Stations),1),'" + Stations.name + "','" + Stations.dia_chi + "'," + Stations.main_stations_id + ",'" + Stations.nguoi_quan_ly + "','" + Stations.so_dien_thoai + "',to_date('" + Stations.ngay_xay_dung + "','dd/mm/yyyy'),0,to_date('" + Stations.ngay_su_dung + "','dd/mm/yyyy'),'" + Stations.ghi_chu + "','" + Stations.ma_tram + "','" + Stations.toa_do + "','" + Stations.cot + "','" + Stations.cap_phep + "','" + Stations.csht + "','" + Stations.loai_csht + "','" + Stations.kieu_csht + "'," + Stations.village_id + ",to_date('" + Stations.ngay_cap_phep + "','dd/mm/yyyy') from dual";
            General.Lib_ExecuteNonQuery(sql);
        }
        public void updateStations(StationsModel Stations)
        {
            string sql = "UPDATE Stations SET name='" + Stations.name + "', dia_chi='" + Stations.dia_chi + "',main_stations_id=" + Stations.main_stations_id + ",nguoi_quan_ly='" + Stations.nguoi_quan_ly + "',so_dien_thoai='" + Stations.so_dien_thoai + "',ngay_xay_dung=to_date('" + Stations.ngay_xay_dung + "','dd/mm/yyyy'),tram_node_b=" + Stations.tram_node_b + ",ngay_su_dung=to_date('" + Stations.ngay_su_dung + "','dd/mm/yyyy'),ghi_chu='" + Stations.ghi_chu+"'";
            sql += ",ma_tram='" + Stations.ma_tram + "',toa_do='" + Stations.toa_do + "',cot='" + Stations.cot + "',cap_phep='" + Stations.cap_phep + "',csht='" + Stations.csht + "',loai_csht='" + Stations.loai_csht + "',kieu_csht='" + Stations.kieu_csht + "',village_id="+Stations.village_id+",ngay_cap_phep=to_date('" + Stations.ngay_cap_phep + "','dd/mm/yyyy') where id=" + Stations.id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public void deleteStations(int id)
        {
            string sql = "DELETE FROM Stations WHERE id=" + id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public int checkExist(StationsModel Stations)
        {
            string sql = "SELECT * FROM Stations WHERE name='" + Stations.name + "'";
            return General.Lib_CheckExist(sql);
        }
        public int checkSubId(int id)
        {
            string sql = "SELECT * FROM Device_Stations WHERE stations_id='" + id + "'";
            return General.Lib_CheckExist(sql);
        }
        public int checkSubId_Fuel_Input(int id)
        {
            string sql = "SELECT * FROM Fuel_Input WHERE stations_id='" + id + "'";
            return General.Lib_CheckExist(sql);
        }

        public List<StationsModel> FindStations(string str)
        {
            List<StationsModel> Stations = new List<StationsModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM Stations WHERE "+str);
            while (odr.Read())
            {
                Stations.Add(new StationsModel
                {
                    id = Convert.ToInt32(odr[0].ToString()),
                    name = odr[1].ToString(),
                    dia_chi = odr[2].ToString(),
                    main_stations_id = Convert.ToInt32(odr[3].ToString()),
                    nguoi_quan_ly = odr[4].ToString(),
                    so_dien_thoai = odr[5].ToString(),
                    ngay_xay_dung = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),
                    tram_node_b = Convert.ToInt16(odr[7].ToString()),
                    ngay_su_dung = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                    ghi_chu = odr[9].ToString(),
                    ma_tram = odr["ma_tram"].ToString(),
                    toa_do = odr["toa_do"].ToString(),
                    cot = odr["cot"].ToString(),
                    cap_phep = odr["cap_phep"].ToString(),
                    csht = odr["csht"].ToString(),
                    loai_csht = odr["loai_csht"].ToString(),
                    kieu_csht = odr["kieu_csht"].ToString(),
                    village_id = odr["village_id"].ToString(),
                    ngay_cap_phep = odr.IsDBNull(18) ? null : odr.GetDateTime(18).ToString("dd/MM/yyyy")
                });
            }
            return Stations;
        }


    }
    //public class Main_StationsModel
    //{
    //    public int id { get; set; }
    //    [Required(ErrorMessage = "Tên trạm không được để trống!")]
    //    public string name { get; set; }
    //    public string dia_chi { get; set; }
    //    public string quan_ly_tt { get; set; }
    //    public string so_dien_thoai { get; set; }
    //    public string ghi_chu { get; set; }

    //    public List<Main_StationsModel> getMain_Stations()
    //    {
    //        List<Main_StationsModel> Main_Stations = new List<Main_StationsModel>();
    //        OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM main_stations order by name");
    //        while (odr.Read())
    //        {
    //            Main_Stations.Add(new Main_StationsModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), dia_chi = odr[2].ToString(), quan_ly_tt = odr[3].ToString(), so_dien_thoai = odr[4].ToString(), ghi_chu = odr[5].ToString() });
    //        }
    //        return Main_Stations;
    //    }
    //    public DataSet bc_Chungloaithietbi()
    //    {
    //        string sql = "SELECT * FROM Main_Sort_Device order by name";
    //        OracleConnection conn = General.ConnectDataBase();
    //        OracleCommand omd = new OracleCommand(sql, conn);
    //        OracleDataAdapter oda = new OracleDataAdapter(omd);
    //        DataSet ds = new DataSet();
    //        try
    //        {
    //            conn.Open();
    //        }
    //        catch
    //        {
    //            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
    //        }
    //        oda.Fill(ds, "0");
    //        conn.Close();
    //        return ds;
    //    }
    //}
    public class StationsAssociateModel
    {
        public int id { get; set; }
        [Required(ErrorMessage = "Tên trạm không được để trống!")]
        public string name { get; set; }
        public string dia_chi { get; set; }
        public string main_stations_name { get; set; }
        public string nguoi_quan_ly { get; set; }
        public string so_dien_thoai { get; set; }
        public string ngay_xay_dung { get; set; }
        public string tram_node_b { get; set; }
        public string ngay_su_dung { get; set; }
        public string ghi_chu { get; set; }

        [Required(ErrorMessage = "Mã trạm không được để trống!")]
        public string ma_tram { get; set; }
        public string toa_do { get; set; }
        public string cot { get; set; }
        public string cap_phep { get; set; }
        public string csht { get; set; }
        public string loai_csht { get; set; }
        public string kieu_csht { get; set; }
        [Required(ErrorMessage = "Không được để trống!")]
        public string village_id { get; set; }
        public string ngay_cap_phep { get; set; }

        public List<StationsAssociateModel> getStationsIndex()
        {
            List<StationsAssociateModel> Stations = new List<StationsAssociateModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                    sql = "SELECT a.id,a.name,a.dia_chi,b.name,a.nguoi_quan_ly,a.so_dien_thoai,a.ngay_xay_dung,a.tram_node_b,a.ngay_su_dung,a.ghi_chu,a.ma_tram,a.toa_do,a.cot,a.cap_phep,a.csht,a.loai_csht,a.kieu_csht,a.village_id,a.ngay_cap_phep FROM Stations a,main_stations b where a.main_stations_id=b.id and b.id="+trungtam_id+" order by b.name,a.name";

                else
                    sql = "SELECT a.id,a.name,a.dia_chi,b.name,a.nguoi_quan_ly,a.so_dien_thoai,a.ngay_xay_dung,a.tram_node_b,a.ngay_su_dung,a.ghi_chu,a.ma_tram,a.toa_do,a.cot,a.cap_phep,a.csht,a.loai_csht,a.kieu_csht,a.village_id,a.ngay_cap_phep FROM Stations a,main_stations b where a.main_stations_id=b.id order by b.name,a.name";
            }
            else
                sql = "SELECT a.id,a.name,a.dia_chi,b.name,a.nguoi_quan_ly,a.so_dien_thoai,a.ngay_xay_dung,a.tram_node_b,a.ngay_su_dung,a.ghi_chu,a.ma_tram,a.toa_do,a.cot,a.cap_phep,a.csht,a.loai_csht,a.kieu_csht,a.village_id,a.ngay_cap_phep FROM Stations a,main_stations b where a.main_stations_id=b.id order by b.name,a.name";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                Stations.Add(new StationsAssociateModel 
                { 
                    id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), dia_chi = odr[2].ToString(), main_stations_name = odr[3].ToString(), nguoi_quan_ly = odr[4].ToString(), so_dien_thoai = odr[5].ToString(), ngay_xay_dung = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"), tram_node_b =  Convert.ToInt16(odr[7].ToString())==0 ? "Không" :"Có", ngay_su_dung = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"), ghi_chu = odr[9].ToString(), 
                    ma_tram = odr["ma_tram"].ToString(),
                    toa_do = odr["toa_do"].ToString(),
                    cot = odr["cot"].ToString(),
                    cap_phep = odr["cap_phep"].ToString(),
                    csht = odr["csht"].ToString(),
                    loai_csht = odr["loai_csht"].ToString(),
                    kieu_csht = odr["kieu_csht"].ToString(),
                    village_id = odr["village_id"].ToString(),
                    ngay_cap_phep = odr.IsDBNull(18) ? null : odr.GetDateTime(18).ToString("dd/MM/yyyy")
                });
            }
            return Stations;
        }
        public List<StationsAssociateModel> FindStations(string str)
        {
            List<StationsAssociateModel> Stations = new List<StationsAssociateModel>();
            string sql = "SELECT a.id,a.name,a.dia_chi,b.name,a.nguoi_quan_ly,a.so_dien_thoai,a.ngay_xay_dung,a.tram_node_b,a.ngay_su_dung,a.ghi_chu,a.ma_tram,a.toa_do,a.cot,a.cap_phep,a.csht,a.loai_csht,a.kieu_csht ,a.village_id,a.ngay_cap_phep FROM Stations a,main_stations b where a.main_stations_id=b.id  AND " + str + " order by b.name,a.name";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                Stations.Add(new StationsAssociateModel 
                { 
                    id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), dia_chi = odr[2].ToString(), main_stations_name = odr[3].ToString(), nguoi_quan_ly = odr[4].ToString(), so_dien_thoai = odr[5].ToString(), ngay_xay_dung = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"), tram_node_b = Convert.ToInt16(odr[7].ToString()) == 0 ? "Không" : "Có", ngay_su_dung = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"), ghi_chu = odr[9].ToString(),
                    ma_tram = odr["ma_tram"].ToString(),
                    toa_do = odr["toa_do"].ToString(),
                    cot = odr["cot"].ToString(),
                    cap_phep = odr["cap_phep"].ToString(),
                    csht = odr["csht"].ToString(),
                    loai_csht = odr["loai_csht"].ToString(),
                    kieu_csht = odr["kieu_csht"].ToString(),
                    village_id = odr["village_id"].ToString(),
                    ngay_cap_phep = odr.IsDBNull(18) ? null : odr.GetDateTime(18).ToString("dd/MM/yyyy")
                });
            }
            return Stations;
        }
    }
    public class KhuvucsModel
    {
        public int makv { get; set; }
        public string donvi_id { get; set; }
        public string ten_kv { get; set; }

        public List<KhuvucsModel> getKhuvucsByMain_Stations(int id)
        {
            List<KhuvucsModel> Khuvucs = new List<KhuvucsModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT makv,donvi_id,ten_kv FROM Khuvucs where donvi_id=" + id + " order by ten_kv");            
            while (odr.Read())
            {
                Khuvucs.Add(new KhuvucsModel
                {
                    makv = Convert.ToInt32(odr[0].ToString()),
                    donvi_id = odr[1].ToString(),
                    ten_kv = odr[2].ToString()                    
                });
            }
            return Khuvucs;
        }
        public List<SelectListItem> getKhuvucsByMain_Stations(string MainStationId, string Order = "ten_kv")
        {
            List<SelectListItem> list = new List<SelectListItem>();
            if (Convert.ToInt32(MainStationId) >= 0)
            {
                string sql = "select makv,ten_kv from Khuvucs where donvi_id=" + Convert.ToInt32(MainStationId) + " order by " + Order;
                OracleDataReader odr = General.Lib_GetDataReader(sql);
                while (odr.Read())
                {
                    list.Add(new SelectListItem { Text = odr[1].ToString(), Value = odr[0].ToString() });
                }
            }
            return list;
        }
    }
}